﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SeguradoraIA.Business;
using System.Data.OleDb;
using System.Data;
using SeguradoraIA.Framework;

namespace SeguradoraIA.DAL
{
    public class EntradaDAL
    {
        public List<Entrada> ObterViaExcel(string path)
        {
            List<Entrada> listaEntrada = new List<Entrada>();
            string query = "SELECT * FROM [Sheet1$]";
            OleDbConnection conn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();

            try
            {
                string connString = string.Empty;

                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

                query = "SELECT * FROM [Sheet1$]";
                conn = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                cmd = new OleDbCommand(query, conn);
                da = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                foreach (DataRow row in dt.Rows)
                {
                    Entrada entrada = new Entrada();

                    entrada.ListaEntrada.Add(row["Bairro"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["CursoFormacao"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["GrauInstrucao"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["Idade"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["MarcaCarro"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["Profissao"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["Resultado"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["Sinistro"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["TempoHabilitacao"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["TipoCarro"].ToString().ToDouble());
                    entrada.ListaEntrada.Add(row["ValorCarro"].ToString().ToDouble());

                    entrada.Resultado = row["Resultado"].ToString().ToDouble();

                    listaEntrada.Add(entrada);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                conn.Close();
                conn.Dispose();
            }

            return listaEntrada;
        }
    }
}